package com.lyf.dao.impl;

import com.lyf.dao.DocumentDao;
import com.lyf.pojo.Document;
import com.lyf.pojo.Job;
import com.lyf.utils.DBTools;
import com.lyf.utils.PageModel;
import com.lyf.vo.CountVoByName;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DocumentDaoImpl implements DocumentDao {
    QueryRunner qr = DBTools.getQueryRunner();
    @Override
    public void uploadFile(Document document) {
        String sql = "insert into document_inf(title,filename,filetype,remark,createdate,userId) values (?,?,?,?,?,?)";
        try {
            qr.update(sql,document.getTitle(),document.getFileName(),document.getFiletype(),
                    document.getRemark(),document.getCreateDate(),document.getUserId());
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void delete(Integer id) {
        String sql = "update document_inf set isdelete = 1 where id = ?";
//        QueryRunner qr = DBTools.getQueryRunner();
        try {
            qr.update(sql,id);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public List<CountVoByName> selectFileCount() {
        StringBuilder sql = new StringBuilder("SELECT filetype as name , COUNT(*) as value FROM document_inf WHERE document_inf.isdelete = 0 GROUP BY filetype");
        List<CountVoByName> countVoByName = null;
        // 执行查询
        try{
            countVoByName =  qr.query(sql.toString(), new BeanListHandler<>(CountVoByName.class));

            return countVoByName;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }


    @Override
    public Document selectById(Integer id) {
//        QueryRunner qr = DBTools.getQueryRunner();
        String sql = "select * from document_inf where id = ?";
        ResultSetHandler<Document> rsh = new BeanHandler<>(Document.class);
        Document document = null;
        try {
            document = qr.query(sql,rsh,id);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return document;
    }

    @Override
    public List<Document> queryDocument(Document document, PageModel model) {
//        QueryRunner qr = DBTools.getQueryRunner();
        StringBuilder sql = new StringBuilder("SELECT * FROM document_inf WHERE 1=1");
        List<Object> params = new ArrayList<>(); // 用于存放参数的列表

        // 根据 对象的属性添加查询条件
        sql.append(" AND isdelete = 0");
        if (document != null && document.getTitle() != null && !document.getTitle().isEmpty()) {
            sql.append(" AND title LIKE ?");
            params.add("%" + document.getTitle() + "%");
        }
        if (document != null && document.getFiletype() != null && !document.getFiletype().isEmpty()) {
            sql.append(" AND filetype = ?");
            params.add(document.getFiletype());
        }
        // 如果有其他条件，以同样的方式添加到 sql 和 params

        // 添加分页的 LIMIT 和 OFFSET
        int pageSize = model.getPageSize();
        int startRow = model.getStartRow();
        sql.append(" LIMIT ? OFFSET ?");
        params.add(pageSize);
        params.add(startRow);

        // 执行查询
        try {
            List<Document> documents = qr.query(sql.toString(), new BeanListHandler<>(Document.class), params.toArray());

            return documents;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    @Override
    public int getTotalDocumentCount(Document document) {
//        QueryRunner qr = DBTools.getQueryRunner();
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM document_inf WHERE 1=1");
        List<Object> params = new ArrayList<>();

        // 根据对象的属性决定是否添加查询条件
        sql.append(" AND isdelete = 0");
        if (document != null && document.getTitle() != null && !document.getTitle().isEmpty()) {
            sql.append(" AND title LIKE ?");
            params.add("%" + document.getTitle() + "%");
        }
        if (document != null && document.getFiletype() != null && !document.getFiletype().isEmpty()) {
            sql.append(" AND filetype = ?");
            params.add(document.getFiletype());
        }
        // 执行查询
        ResultSetHandler<Long> countHandler = new ScalarHandler<>();
        try{
            Long count = qr.query(sql.toString(), countHandler, params.toArray());

            return count.intValue();
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}
